<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;

use App\Services\MemberService;
use Illuminate\Http\Request;

use Illuminate\Support\Facades\Auth;

use Illuminate\Support\Facades\DB;

use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Cache;
use Carbon\Carbon;

class DashboardController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth:admin');
    }


    /**
     * @param Request $request
     * @return \Illuminate\Contracts\Foundation\Application|\Illuminate\Contracts\View\Factory|\Illuminate\Http\RedirectResponse|\Illuminate\View\View
     */
    public function index(Request $request)
    {
        // 客服重定向
        if (Auth::user()->role == 2) {
            return redirect()->route('admin.customService.showUserUpgrade');
        }
        $date = $request->get('date', date("Y-m-d"));
        $startTime = $date.' 00:00:00';
        $endTime = $date.' 23:59:59';
        $yesterdayStart = date("Y-m-d", strtotime($startTime)-1).' 00:00:01';
        $yesterdayEnd = date("Y-m-d", strtotime($startTime)-1).' 23:59:59';
        //查询总共发布条数
        $games = DB::table('game_data')
            ->count();

        // 用户
        $user = DB::table('users')
            ->count();


        // 昨日抽奖人数
        $yesterdayDrawNum = DB::table('draw_log')
            ->where('draw_id', '>', 0)
            ->where('create_time', '>=', $yesterdayStart)
            ->where('create_time', '<=',$yesterdayEnd)
            ->count();
        $todayDrawNum = DB::table('draw_log')
            ->where('draw_id', '>', 0)
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<=',$endTime)
            ->count();



        $usersLoginList = DB::table('game_login')
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<=',$endTime)
            ->select(DB::raw('count(id) as count'))
            ->groupBy('unionid')
            ->get();
        $userLoginCount = count($usersLoginList);

        $yUsersLoginList = DB::table('game_login')
            ->where('create_time', '>=', $yesterdayStart)
            ->where('create_time', '<=',$yesterdayEnd)
            ->select(DB::raw('count(id) as count'))
            ->groupBy('unionid')
            ->get();
        $yesterdayUserLoginCount = count($yUsersLoginList);




        $yesterdayDhNum = DB::table('user_balance_log')
            ->where('balance_type', '=', 1)
            ->where('from_type', '=', 2)
            ->where('create_time', '>=', strtotime($yesterdayStart))
            ->where('create_time', '<=',strtotime($yesterdayEnd))
            ->sum('change')/100;
        $todayDhNum = DB::table('user_balance_log')
            ->where('balance_type', '=', 1)
            ->where('from_type', '=', 2)
            ->where('create_time', '>=', strtotime($startTime))
            ->where('create_time', '<=',strtotime($endTime))
            ->sum('change')/100;

        // 充值金额
        $yesterdayRecharge = DB::table('user_recharge_order')
            ->where('pay_status', 1)
            ->where('create_time', '>=', strtotime($yesterdayStart))
            ->where('create_time', '<=',strtotime($yesterdayEnd))
            ->sum('money');
        $todayRecharge = DB::table('user_recharge_order')
            ->where('pay_status', 1)
            ->where('create_time', '>=', strtotime($startTime))
            ->where('create_time', '<=',strtotime($endTime))
            ->sum('money');



        //游戏内赠送积分
        $yesterdayDhjifen = DB::table('user_balance_log')
            // ->where('balance_type', '=', MemberService::BalanceTypeWf)
            ->where('balance_type', '=', 3)
            ->where('from_type', '=', 10)
            ->where('create_time', '>=', strtotime($yesterdayStart))
            ->where('create_time', '<=',strtotime($yesterdayEnd))
            ->sum('change');
        $todayDhjifen = DB::table('user_balance_log')
            ->where('balance_type', '=', 3)
            ->where('from_type', '=', 10)
            ->where('create_time', '>=', strtotime($startTime))
            ->where('create_time', '<=',strtotime($endTime))
            ->sum('change');

        // 新用户
        $yesterdayUsers = DB::table('users')
            ->where('created_at', '>=', $yesterdayStart)
            ->where('created_at', '<=',$yesterdayEnd)
            ->count();
        $todayUsers = DB::table('users')
            ->where('created_at', '>=', $startTime)
            ->where('created_at', '<=',$endTime)
            ->count();

        //玩服积分
        $yesterdayWfjifen = DB::table('user_balance_log')
            ->selectRaw('sum(`change`) total, from_type')
            ->where('balance_type', '=', MemberService::BalanceTypeWf)
            ->where('change', '>', 0)
            ->where('create_time', '>=', strtotime($yesterdayStart))
            ->where('create_time', '<=',strtotime($yesterdayEnd))
            ->groupBy('from_type')
            ->pluck('total', 'from_type');
        $todayWfjifen = DB::table('user_balance_log')
            ->selectRaw('sum(`change`) total, from_type')
            ->where('balance_type', '=', MemberService::BalanceTypeWf)
            ->where('change', '>', 0)
            ->where('create_time', '>=', strtotime($startTime))
            ->where('create_time', '<=',strtotime($endTime))
            ->groupBy('from_type')
            ->pluck('total', 'from_type');

        //消耗玩服积分
        $yesterdayXhWfjifen = DB::table('user_balance_log')
        ->where('balance_type', '=', 3)
        ->where('from_type', '=', 2)
            ->where('change', '<', 0)
            ->where('create_time', '>=', strtotime($yesterdayStart))
            ->where('create_time', '<=',strtotime($yesterdayEnd))
            ->sum('change');
        $todayXhWfjifen = DB::table('user_balance_log')
        ->where('balance_type', '=', 3)
        ->where('from_type', '=', 2)
            ->where('change', '<', 0)
            ->where('create_time', '>=', strtotime($startTime))
            ->where('create_time', '<=',strtotime($endTime))
            ->sum('change');



        return view('admin.dashboard.index', compact(
            'games', 'user', 'userLoginCount',
            'yesterdayDrawNum', 'yesterdayDhNum', 'todayDhNum', 'yesterdayRecharge', 'todayRecharge',
            'yesterdayUsers', 'todayUsers', 'yesterdayUserLoginCount', 'todayDrawNum', 'date','yesterdayDhjifen',"todayDhjifen",
            'yesterdayWfjifen', 'todayWfjifen','yesterdayXhWfjifen', 'todayXhWfjifen'
        ));
    }

     /**
     * 柱状图
     */


    public function getData(Request $request)
    {
        try {
            $startDate = $request->get('start_date', date("Y-m-d", strtotime("-6 days")));
            $endDate = $request->get('end_date', date("Y-m-d"));

            // Ensure date range is within 30 days
            $dateDiff = (new \DateTime($endDate))->diff(new \DateTime($startDate))->days;
            if ($dateDiff > 30) {
                return response()->json(['error' => 'Date range cannot exceed 30 days'], 400);
            }

            $startTime = Carbon::createFromFormat('Y-m-d', $startDate)->startOfDay();
            $endTime = Carbon::createFromFormat('Y-m-d', $endDate)->endOfDay();

            // Cache key
            $cacheKey = "dashboard_data_{$startDate}_{$endDate}";
            $data = Cache::remember($cacheKey, 60, function() use ($startTime, $endTime) {
                $dates = [];
                $newUsers = [];
                $gamePlayers = [];
                $rechargeAmount = [];
                $exchangeBalance = [];
                $gameActivityPoints = [];
                $usedGamePoints = [];
                $userSpendAmount = [];
                $returningUsers = [];  // 新增回归用户数组

                $currentDate = clone $startTime;
                $end = clone $endTime;

                while ($currentDate <= $end) {
                    $dateStr = $currentDate->format("Y-m-d");
                    $currentStart = $currentDate->copy()->startOfDay();
                    $currentEnd = $currentDate->copy()->endOfDay();

                    $dates[] = $dateStr;

                    $newUsers[] = DB::table('users')
                        ->where('created_at', '>=', $currentStart)
                        ->where('created_at', '<=', $currentEnd)
                        ->count();

                    $gamePlayers[] = DB::table('game_login')
                        ->where('create_time', '>=', $currentStart)
                        ->where('create_time', '<=', $currentEnd)
                        ->select(DB::raw('count(id) as count'))
                        ->groupBy('unionid')
                        ->get()
                        ->count();

                    $rechargeAmount[] = DB::table('user_recharge_order')
                        ->where('pay_status', 1)
                        ->where('create_time', '>=', strtotime($currentStart))
                        ->where('create_time', '<=', strtotime($currentEnd))
                        ->sum('money');

                    $exchangeBalance[] = DB::table('user_balance_log')
                        ->where('balance_type', '=', 1)
                        ->where('from_type', '=', 2)
                        ->where('create_time', '>=', strtotime($currentStart))
                        ->where('create_time', '<=', strtotime($currentEnd))
                        ->sum('change') / 100;

                    $gameActivityPoints[] = DB::table('user_balance_log')
                        ->where('balance_type', '=', 3)
                        ->where('from_type', '=', 10)
                        ->where('create_time', '>=', strtotime($currentStart))
                        ->where('create_time', '<=', strtotime($currentEnd))
                        ->sum('change');

                    $usedGamePoints[] = DB::table('user_balance_log')
                        ->where('balance_type', '=', 3)
                        ->where('from_type', '=', 2)
                        ->where('change', '<', 0)
                        ->where('create_time', '>=', strtotime($currentStart))
                        ->where('create_time', '<=', strtotime($currentEnd))
                        ->sum('change');

                    $dailyDrawNum[] = DB::table('draw_log')
                    ->where('draw_id', '>', 0)
                    ->where('game_num', '<>', '')
                    ->where('create_time', '>=', $currentStart)
                    ->where('create_time', '<=', $currentEnd)
                    ->count();

                    // 回归用户数量查询 (新增)
                    $returningUsers[] = DB::table('users_tghuigui')
                        ->where('create_time', '>=', $currentStart)
                        ->where('create_time', '<=', $currentEnd)
                        ->count();  // 获取当天的回归用户数量
                    // ->whereDate('game_recharge.create_time', $today)
                    $today =  now()->format('Y-m-d');
                    $rechargeDataToday = DB::table('game_recharge')
                    ->join('game_data', 'game_data.server_name', '=', 'game_recharge.version_name')
                    ->where('game_recharge.create_time', '>=', $currentStart)
                    ->where('game_recharge.create_time', '<=', $currentEnd)
                    ->select('game_data.server_name', DB::raw('SUM(game_recharge.amount) as total_amount'))
                    ->groupBy('game_data.server_name')
                    ->orderByDesc('total_amount')
                    ->get(); // 获取查询结果

                    $userSpend = $rechargeDataToday->sum('total_amount');
                    $userSpendAmount[] = $userSpend;

                    $currentDate->addDay();
                }

                $games = DB::table('game_data')->count();
                $user = DB::table('users')->count();

                return [
                    'dates' => $dates,
                    'newUsers' => $newUsers,
                    'gamePlayers' => $gamePlayers,
                    'rechargeAmount' => $rechargeAmount,
                    'exchangeBalance' => $exchangeBalance,
                    'gameActivityPoints' => $gameActivityPoints,
                    'usedGamePoints' => $usedGamePoints,
                    'userSpendAmount' => $userSpendAmount,
                    'returningUsers' => $returningUsers,  // 返回回归用户数量
                    'totalGames' => $games,
                    'totalUsers' => $user,
                    'dailyDrawNum' => $dailyDrawNum
                ];
            });

            return response()->json($data);

        } catch (\Exception $e) {
            Log::error('Error fetching dashboard data: ' . $e->getMessage());
            return response()->json(['error' => 'Error fetching data'], 500);
        }
    }


    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }

    public function getLoginRechargeStatisticPage()
    {
        return view('admin.dashboard.login_recharge');
    }


//  public function getGameRechargeStatistics(Request $request)
//     {
//         try {
//             $date = $request->input('date');
//             if (!$date) {
//                 return response()->json(['error' => 'Date parameter is required'], 400);
//             }

//             $today = date('Y-m-d', strtotime($date));
//             $monthStart = date('Y-m-01', strtotime($date));
//             $threeDaysAgo = date('Y-m-d', strtotime($date . ' -2 days'));
//             $nextDay = date('Y-m-d', strtotime($today . ' +1 day'));

//             // 当天所有游戏有充值金额的环状图及占比
//             $rechargeDataToday = DB::table('game_recharge')
//                 ->join('game_data', 'game_data.server_name', '=', 'game_recharge.version_name')
//                 ->whereDate('game_recharge.create_time', $today)
//                 ->select('game_data.server_name', DB::raw('SUM(game_recharge.amount) as total_amount'))
//                 ->groupBy('game_data.server_name')
//                 ->orderByDesc('total_amount')
//                 ->get();

//             $totalAmount = $rechargeDataToday->sum('total_amount');
//             $pieData = [];
//             $dayBarData = ['names' => [], 'values' => []];
//             foreach ($rechargeDataToday as $data) {
//                 $pieData[] = ['name' => $data->server_name, 'value' => $data->total_amount];
//                 $dayBarData['names'][] = $data->server_name;
//                 $dayBarData['values'][] = $data->total_amount;
//             }

//             // 最近7个游戏近3日游戏充值柱状图
//             $recentGames = DB::table('game_data')
//                 ->orderBy('create_time', 'desc')
//                 ->limit(7)
//                 ->pluck('server_name');

//             $recentGamesRecharge = DB::table('game_recharge')
//                 ->whereIn('version_name', $recentGames)
//                 ->whereBetween('create_time', [$threeDaysAgo, $nextDay])
//                 ->select('version_name', DB::raw('DATE(create_time) as date'), DB::raw('SUM(amount) as total_amount'))
//                 ->groupBy('version_name', 'date')
//                 ->get();

//             $recentGamesBarData = ['names' => [], 'values' => []];
//             foreach ($recentGames as $game) {
//                 $recentGamesBarData['names'][] = $game;
//                 $recentGamesBarData['values'][] = [
//                     'name' => $game,
//                     'values' => [
//                         $recentGamesRecharge->where('version_name', $game)->where('date', $threeDaysAgo)->sum('total_amount'),
//                         $recentGamesRecharge->where('version_name', $game)->where('date', date('Y-m-d', strtotime($date . ' -1 days')))->sum('total_amount'),
//                         $recentGamesRecharge->where('version_name', $game)->where('date', $today)->sum('total_amount')
//                     ]
//                 ];
//             }

//             // 当月1号起到当日所有游戏的充值金额汇总统计柱状图
//             $monthlyRecharge = DB::table('game_recharge')
//                 ->whereBetween('create_time', [$monthStart, $today])
//                 ->select(DB::raw('DATE(create_time) as date'), DB::raw('SUM(amount) as total_amount'))
//                 ->groupBy('date')
//                 ->orderBy('date', 'asc') // 按日期排序
//                 ->get();

//             $monthlyBarData = ['names' => [], 'values' => []];
//             foreach ($monthlyRecharge as $data) {
//                 $monthlyBarData['names'][] = $data->date;
//                 $monthlyBarData['values'][] = $data->total_amount;
//             }

//             // 红包版本数据 - 最近5个红包版本，日期为奇数时为红包版本
//             $redPacketVersions = DB::table('game_data')
//                 ->whereRaw('(DAY(create_time) + 1) % 2 = 0')
//                 ->orderBy('create_time', 'desc')
//                 ->limit(5)
//                 ->pluck('server_name')
//                 ->toArray();

//             $redPacketRecharge = DB::table('game_recharge')
//                 ->join('game_data', 'game_data.server_name', '=', 'game_recharge.version_name')
//                 ->whereIn('game_recharge.version_name', $redPacketVersions)
//                 ->whereBetween('game_recharge.create_time', [$threeDaysAgo, $nextDay])
//                 ->select('game_recharge.version_name', DB::raw('DATE(game_recharge.create_time) as date'), DB::raw('SUM(game_recharge.amount) as total_amount'))
//                 ->groupBy('game_recharge.version_name', 'date')
//                 ->orderBy('game_recharge.version_name')
//                 ->orderBy('date', 'asc')
//                 ->get();

//             $redPacketBarData = ['names' => [], 'values' => []];
//             foreach ($redPacketVersions as $version) {
//                 $redPacketBarData['names'][] = $version;
//                 $redPacketBarData['values'][] = [
//                     'name' => $version,
//                     'values' => [
//                         $redPacketRecharge->where('version_name', $version)->where('date', $threeDaysAgo)->sum('total_amount'),
//                         $redPacketRecharge->where('version_name', $version)->where('date', date('Y-m-d', strtotime($date . ' -1 days')))->sum('total_amount'),
//                         $redPacketRecharge->where('version_name', $version)->where('date', $today)->sum('total_amount')
//                     ]
//                 ];
//             }

//             // 每个游戏的登录人数及占比
//             $loginData = DB::table('game_login')
//                 ->join('game_data', 'game_data.server_name', '=', 'game_login.version_name')
//                 ->whereDate('game_login.create_time', $today)
//                 ->select('game_data.server_name', DB::raw('COUNT(DISTINCT game_login.unionid) as login_count'))
//                 ->groupBy('game_data.server_name')
//                 ->orderByDesc('login_count')
//                 ->get();

//             $totalLogins = $loginData->sum('login_count');
//             $loginPieData = [];
//             foreach ($loginData as $data) {
//                 $loginPieData[] = ['name' => $data->server_name, 'value' => $data->login_count];
//             }

//             return response()->json([
//                 'pieData' => $pieData,
//                 'dayBarData' => $dayBarData,
//                 'recentGamesBarData' => $recentGamesBarData,
//                 'monthlyBarData' => $monthlyBarData,
//                 'redPacketBarData' => $redPacketBarData,
//                 'loginPieData' => $loginPieData,
//                 'totalAmount' => $totalAmount,
//                 'totalLogins' => $totalLogins
//             ]);
//         } catch (\Exception $e) {
//             Log::error('Error fetching game recharge statistics: ' . $e->getMessage(), ['exception' => $e]);
//             return response()->json(['error' => 'Internal Server Error'], 500);
//         }
//     }

//并发接口
    public function getRechargePieData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $cacheKey = "recharge_pie_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        $rechargeDataToday = DB::table('game_recharge')
            ->join('game_data', 'game_data.server_name', '=', 'game_recharge.version_name')
            ->whereDate('game_recharge.create_time', $today)
            ->select('game_data.server_name', DB::raw('SUM(game_recharge.amount) as total_amount'))
            ->groupBy('game_data.server_name')
            ->orderByDesc('total_amount')
            ->get();

        $totalAmount = $rechargeDataToday->sum('total_amount');
        $pieData = [];
        foreach ($rechargeDataToday as $data) {
            $pieData[] = ['name' => $data->server_name, 'value' => $data->total_amount];
        }

        $result = [
            'pieData' => $pieData,
            'totalAmount' => $totalAmount,
        ];

        Cache::put($cacheKey, $result, 60);

        return response()->json($result);
    } catch (\Exception $e) {
        Log::error('Error fetching recharge pie data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}

//当日充值金额前10名数据
public function getDayBarData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $cacheKey = "day_bar_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        $rechargeDataToday = DB::table('game_recharge')
            ->join('game_data', 'game_data.server_name', '=', 'game_recharge.version_name')
            ->whereDate('game_recharge.create_time', $today)
            ->select('game_data.server_name', DB::raw('SUM(game_recharge.amount) as total_amount'))
            ->groupBy('game_data.server_name')
            ->orderByDesc('total_amount')
            ->get();

        $dayBarData = ['names' => [], 'values' => [], 'login_counts' => [], 'high_spenders' => [], 'high_spenders_50' => [], 'percent_login_counts' => [], 'percent_high_spenders_50' => []];

        foreach ($rechargeDataToday as $data) {
            $dayBarData['names'][] = $data->server_name;
            $dayBarData['values'][] = $data->total_amount;

            $loginCount = DB::table('game_recharge')
                ->where('version_name', $data->server_name)
                ->whereDate('create_time', $today)
                ->distinct('unionid')
                ->count('unionid');

            $highSpendersCount = DB::table('game_recharge')
                ->where('version_name', $data->server_name)
                ->whereDate('create_time', $today)
                ->select('unionid', DB::raw('SUM(amount) as total_amount'))
                ->groupBy('unionid')
                ->having('total_amount', '>', 20)
                ->get()
                ->count();

            $highSpendersCount50 = DB::table('game_recharge')
                ->where('version_name', $data->server_name)
                ->whereDate('create_time', $today)
                ->select('unionid', DB::raw('SUM(amount) as total_amount'))
                ->groupBy('unionid')
                ->having('total_amount', '>', 50)
                ->get()
                ->count();

            $totalLogins = DB::table('game_login')
                ->where('version_name', $data->server_name)
                ->whereDate('create_time', $today)
                ->distinct('unionid')
                ->count('unionid');

            $percentLoginCounts = $totalLogins > 0 ? round(($loginCount / $totalLogins) * 100, 2) : 0;
            $percentHighSpenders50 = $totalLogins > 0 ? round(($highSpendersCount50 / $totalLogins) * 100, 2) : 0;

            $dayBarData['login_counts'][] = $loginCount;
            $dayBarData['high_spenders'][] = $highSpendersCount;
            $dayBarData['high_spenders_50'][] = $highSpendersCount50;
            $dayBarData['percent_login_counts'][] = $percentLoginCounts;
            $dayBarData['percent_high_spenders_50'][] = $percentHighSpenders50;
        }

        Cache::put($cacheKey, $dayBarData, 60);

        return response()->json($dayBarData);
    } catch (\Exception $e) {
        Log::error('Error fetching day bar data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}

//最近7个游戏近3日游戏充值数据
public function getRecentGamesBarData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $threeDaysAgo = date('Y-m-d', strtotime($date . ' -2 days'));
        $nextDay = date('Y-m-d', strtotime($today . ' +1 day'));

        $cacheKey = "recent_games_bar_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        $recentGames = DB::table('game_data')
            ->orderBy('create_time', 'desc')
            ->limit(7)
            ->pluck('server_name');

        $recentGamesRecharge = DB::table('game_recharge')
            ->whereIn('version_name', $recentGames)
            ->whereBetween('create_time', [$threeDaysAgo, $nextDay])
            ->select('version_name', DB::raw('DATE(create_time) as date'), DB::raw('SUM(amount) as total_amount'))
            ->groupBy('version_name', 'date')
            ->get();

        $recentGamesBarData = ['names' => [], 'values' => []];
        foreach ($recentGames as $game) {
            $recentGamesBarData['names'][] = $game;
            $recentGamesBarData['values'][] = [
                'name' => $game,
                'values' => [
                    $recentGamesRecharge->where('version_name', $game)->where('date', $threeDaysAgo)->sum('total_amount'),
                    $recentGamesRecharge->where('version_name', $game)->where('date', date('Y-m-d', strtotime($date . ' -1 days')))->sum('total_amount'),
                    $recentGamesRecharge->where('version_name', $game)->where('date', $today)->sum('total_amount')
                ]
            ];
        }

        Cache::put($cacheKey, $recentGamesBarData, 60);

        return response()->json($recentGamesBarData);
    } catch (\Exception $e) {
        Log::error('Error fetching recent games bar data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}

//当月1号起到当日所有游戏的充值金额汇总数据
 public function getMonthlyBarData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $monthStart = date('Y-m-01', strtotime($date));

        $cacheKey = "monthly_bar_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        $monthlyRecharge = DB::table('game_recharge')
            ->whereBetween('create_time', [$monthStart, $today])
            ->select(DB::raw('DATE(create_time) as date'), DB::raw('SUM(amount) as total_amount'))
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();

        $monthlyBarData = ['names' => [], 'values' => []];
        foreach ($monthlyRecharge as $data) {
            $monthlyBarData['names'][] = $data->date;
            $monthlyBarData['values'][] = $data->total_amount;
        }

        Cache::put($cacheKey, $monthlyBarData, 60);

        return response()->json($monthlyBarData);
    } catch (\Exception $e) {
        Log::error('Error fetching monthly bar data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}

//历史数据和当月游戏数据
public function getTotalRechargeData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $monthStart = date('Y-m-01', strtotime($date));
        $cacheKey = "total_recharge_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        // 获取所有游戏的 server_name
        $serverNames = DB::table('game_data')->pluck('server_name')->toArray();

        // 查询所有时间段内的充值总额
        $totalRechargeAllTime = DB::table('game_recharge')
            ->join('game_data', 'game_recharge.version_name', '=', 'game_data.server_name')
            ->whereIn('game_recharge.version_name', $serverNames)
            ->where('game_recharge.create_time', '>=', DB::raw('game_data.create_time'))
            ->select('game_recharge.version_name', DB::raw('SUM(game_recharge.amount) as total_amount'))
            ->groupBy('game_recharge.version_name')
            ->orderBy('total_amount', 'desc')
            ->get();
        // $totalRechargeAllTime = DB::table('game_recharge')
        //     ->whereIn('version_name', $serverNames)
        //     ->select('version_name', DB::raw('SUM(amount) as total_amount'))
        //     ->groupBy('version_name')
        //     ->orderBy('total_amount', 'desc')
        //     ->get();

        // 查询当月内的充值总额
        $totalRechargeThisMonth = DB::table('game_recharge')
            ->whereIn('version_name', $serverNames)
            ->whereBetween('create_time', [$monthStart, $today])
            ->select('version_name', DB::raw('SUM(amount) as monthly_amount'))
            ->groupBy('version_name')
            ->orderBy('monthly_amount', 'desc')
            ->get();

        // 处理数据并计算占比
        $data = $totalRechargeAllTime->map(function ($item) use ($totalRechargeThisMonth) {
            $monthlyData = $totalRechargeThisMonth->firstWhere('version_name', $item->version_name);
            $item->monthly_amount = $monthlyData ? $monthlyData->monthly_amount : 0;
            $item->monthly_percentage = $item->total_amount > 0 ? round(($item->monthly_amount / $item->total_amount) * 100, 2) : 0;
            return $item;
        });

        $result = [
            'totalRechargeData' => $data,
        ];

        Cache::put($cacheKey, $result, 60);

        return response()->json($result);
    } catch (\Exception $e) {
        Log::error('Error fetching total recharge data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}


 //最近5个红包版本近3日充值数据
public function getRedPacketBarData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $threeDaysAgo = date('Y-m-d', strtotime($date . ' -2 days'));
        $nextDay = date('Y-m-d', strtotime($today . ' +1 day'));

        $cacheKey = "red_packet_bar_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        $redPacketVersions = DB::table('game_data')
            ->whereRaw('(DAY(create_time) + 1) % 2 = 0')
            ->orderBy('create_time', 'desc')
            ->limit(5)
            ->pluck('server_name')
            ->toArray();

        $redPacketRecharge = DB::table('game_recharge')
            ->join('game_data', 'game_data.server_name', '=', 'game_recharge.version_name')
            ->whereIn('game_recharge.version_name', $redPacketVersions)
            ->whereBetween('game_recharge.create_time', [$threeDaysAgo, $nextDay])
            ->select('game_recharge.version_name', DB::raw('DATE(game_recharge.create_time) as date'), DB::raw('SUM(game_recharge.amount) as total_amount'))
            ->groupBy('game_recharge.version_name', 'date')
            ->orderBy('game_recharge.version_name')
            ->orderBy('date', 'asc')
            ->get();

        $redPacketBarData = ['names' => [], 'values' => []];
        foreach ($redPacketVersions as $version) {
            $redPacketBarData['names'][] = $version;
            $redPacketBarData['values'][] = [
                'name' => $version,
                'values' => [
                    $redPacketRecharge->where('version_name', $version)->where('date', $threeDaysAgo)->sum('total_amount'),
                    $redPacketRecharge->where('version_name', $version)->where('date', date('Y-m-d', strtotime($date . ' -1 days')))->sum('total_amount'),
                    $redPacketRecharge->where('version_name', $version)->where('date', $today)->sum('total_amount')
                ]
            ];
        }

        Cache::put($cacheKey, $redPacketBarData, 60);

        return response()->json($redPacketBarData);
    } catch (\Exception $e) {
        Log::error('Error fetching red packet bar data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}

//每个游戏的登录人数及占比
public function getLoginPieData(Request $request)
{
    try {
        $date = $request->input('date');

        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $cacheKey = "login_pie_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        $loginData = DB::table('game_login')
            ->join('game_data', 'game_data.server_name', '=', 'game_login.version_name')
            ->whereDate('game_login.create_time', $today)
            ->select('game_data.server_name', DB::raw('COUNT(DISTINCT game_login.unionid) as login_count'))
            ->groupBy('game_data.server_name')
            ->orderByDesc('login_count')
            ->get();

        $totalLogins = $loginData->sum('login_count');
        $loginPieData = [];
        foreach ($loginData as $data) {
            $loginPieData[] = ['name' => $data->server_name, 'value' => $data->login_count];
        }

        $result = [
            'loginPieData' => $loginPieData,
            'totalLogins' => $totalLogins,
        ];

        Cache::put($cacheKey, $result, 60);

        return response()->json($result);
    } catch (\Exception $e) {
        Log::error('Error fetching login pie data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}



//当日充值消费20元和50元人数及占比

public function getHighSpendersData(Request $request)
{
    try {
        $date = $request->input('date');
        if (!$date) {
            return response()->json(['error' => 'Date parameter is required'], 400);
        }

        $today = date('Y-m-d', strtotime($date));
        $cacheKey = "high_spenders_data_{$today}";

        if (Cache::has($cacheKey)) {
            return response()->json(Cache::get($cacheKey));
        }

        // 查询登录人数
        $totalLogins = DB::table('game_login')
            ->whereDate('create_time', $today)
            ->distinct('unionid')
            ->count('unionid');

        // 查询消费数据
        // $rechargeData = DB::table('game_recharge')
        //     ->select(
        //         'version_name',
        //         DB::raw('SUM(amount) as total_amount'),
        //         DB::raw('COUNT(DISTINCT CASE WHEN total_amount >= 20 THEN unionid ELSE NULL END) as high_spenders_20'),
        //         DB::raw('COUNT(DISTINCT CASE WHEN total_amount >= 50 THEN unionid ELSE NULL END) as high_spenders_50'),
        //         DB::raw('COUNT(DISTINCT CASE WHEN total_amount >= 100 THEN unionid ELSE NULL END) as high_spenders_100'),
        //         DB::raw('COUNT(DISTINCT unionid) as total_consumers')
        //     )
        //     ->whereDate('create_time', $today)
        //     ->groupBy('version_name')
        //     // ->from(DB::raw('(SELECT version_name, unionid, SUM(amount) as total_amount FROM game_recharge WHERE DATE(create_time) = "'.$today.'" GROUP BY version_name, unionid) as subquery'))
        //     ->get();

        // // 统计总人数
        // $totalConsumers = $rechargeData->sum('total_consumers');
        // $highSpenders20 = $rechargeData->sum('high_spenders_20');
        // $highSpenders50 = $rechargeData->sum('high_spenders_50');
        // $highSpenders100 = $rechargeData->sum('high_spenders_100');

        $rechargeData = DB::table('game_recharge')
            ->select(
                'version_name',
                DB::raw('SUM(amount) as total_amount'),
                DB::raw('COUNT(DISTINCT CASE WHEN amount >= 20 THEN unionid ELSE NULL END) as high_spenders_20'),
                DB::raw('COUNT(DISTINCT CASE WHEN amount >= 50 THEN unionid ELSE NULL END) as high_spenders_50'),
                DB::raw('COUNT(DISTINCT CASE WHEN amount >= 100 THEN unionid ELSE NULL END) as high_spenders_100'),
                DB::raw('COUNT(DISTINCT unionid) as total_consumers')
            )
            ->whereDate('create_time', $today)
            ->groupBy('version_name')
            ->get();

        // 统计总人数
        $totalConsumers = $rechargeData->sum('total_consumers');
        $highSpenders20 = $rechargeData->sum('high_spenders_20');
        $highSpenders50 = $rechargeData->sum('high_spenders_50');
        $highSpenders100 = $rechargeData->sum('high_spenders_100');




        // 计算百分比
        $percentHighSpenders20 = $totalLogins > 0 ? round(($highSpenders20 / $totalLogins) * 100, 2) : 0;
        $percentHighSpenders50 = $totalLogins > 0 ? round(($highSpenders50 / $totalLogins) * 100, 2) : 0;
        $percentHighSpenders100 = $totalLogins > 0 ? round(($highSpenders100 / $totalLogins) * 100, 2) : 0;

        // 查询游戏中消费人数大于20%登录人数的游戏
        $gamesWith20PercentSpenders = DB::table('game_recharge')
            ->select('version_name', DB::raw('COUNT(DISTINCT unionid) as spenders'))
            ->whereDate('create_time', $today)
            ->groupBy('version_name')
            ->havingRaw('(COUNT(DISTINCT unionid) / (SELECT COUNT(DISTINCT unionid) FROM game_login WHERE version_name = game_recharge.version_name AND DATE(create_time) = ?)) > 0.2', [$today])
            ->get();

        // 查询有人消费大于100元的游戏
           $gamesWith100Spenders = DB::table('game_recharge')
                ->select('version_name', DB::raw('COUNT(DISTINCT unionid) as spenders'))
                ->whereDate('create_time', $today)
                ->groupBy('version_name')
                ->whereIn('unionid', function($query) use ($today) {
                    $query->select('unionid')
                          ->from('game_recharge')
                          ->whereDate('create_time', $today)
                          ->groupBy('unionid', 'version_name')
                          ->havingRaw('SUM(amount) >= 100');
                })
                ->get();


        $result = [
            'highSpenders20' => $highSpenders20,
            'percentHighSpenders20' => $percentHighSpenders20,
            'highSpenders50' => $highSpenders50,
            'percentHighSpenders50' => $percentHighSpenders50,
            'highSpenders100' => $highSpenders100,
            'percentHighSpenders100' => $percentHighSpenders100,
            'totalConsumers' => $totalConsumers,
            'gamesWith20PercentSpenders' => $gamesWith20PercentSpenders,
            'gamesWith100Spenders' => $gamesWith100Spenders,
            'totalLogins' => $totalLogins,
        ];

        Cache::put($cacheKey, $result, 60); // 缓存60分钟

        return response()->json($result);
    } catch (\Exception $e) {
        Log::error('Error fetching high spenders data: ' . $e->getMessage(), ['exception' => $e]);
        return response()->json(['error' => 'Internal Server Error'], 500);
    }
}






    /**
     * 登录充值明细
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     * @throws \Illuminate\Validation\ValidationException
     */
    public function getLoginRechargeStatistic(Request $request)
    {
        $this->validate($request, [
            'st' => ['required', 'date'],
            'et' => ['required', 'date'],
            'page' => ['required', 'int'],
            'page_size' => ['required', 'int'],
        ]);
        $st = $request->st;
        $et = $request->et;
        $area = $request->area;
        $comefrom = $request->comeform;

        if (strtotime($et) < strtotime($st)) {
            return errorResp('时间范围错误');
        }
        if (strtotime($et) - strtotime($st) > (86400*32)) {
            return errorResp('时间范围最大一个月');
        }

        $st .= ' 00:00:00';
        $et .= ' 23:59:59';

        $where = [];
        if ($area) {
            $where[] = ['area', 'like',"%{$area}%"];
        }
        if ($comefrom) {
            $where[] = ['comeform', '=',$comefrom];
        }
        $subQuery = DB::table('game_login')
            ->selectRaw('comeform, area, game_num, count(*) total, date(create_time) d')
            ->where('create_time', '>=', $st)
            ->where('create_time', '<=', $et)
            ->where('comeform', '<>', '0')
            ->where($where)
            ->groupByRaw('comeform, area, game_num, d');
        $loginData = DB::table('game_login')
            ->selectRaw('comeform, area, game_num, count(*) total, date(create_time) d')
            ->where('create_time', '>=', $st)
            ->where('create_time', '<=', $et)
            ->where('comeform', '<>', '0')
            ->where($where)
            ->groupByRaw('comeform, area, game_num, d')
            ->offset(($request->page - 1)*$request->page_size)
            ->limit($request->page_size)
            ->get();
        $total = DB::table(DB::raw("({$subQuery->toSql()}) as sub"))
            ->select(DB::raw('count(*) total'))
            ->mergeBindings($subQuery)
            ->value('total');
        $gameNums = [];
        foreach ($loginData as $item) {
            $gameNums[] = $item->game_num;
        }
        $rechargeData = DB::table('game_recharge')
            ->selectRaw('comeform, area, game_num, sum(amount) total')
            ->where('create_time', '>=', $st)
            ->where('create_time', '<=', $et)
            ->where('comeform', '<>', '0')
            ->where($where)
            ->whereIn('game_num', $gameNums)
            ->groupByRaw('comeform, area, game_num')
            ->get();
        $rechargeMap = [];
        foreach ($rechargeData as $item) {
            $rechargeMap[$item->comeform][$item->game_num][$item->area] = $item->total;
        }
        foreach ($loginData as &$item) {
            $item->recharge_total = $rechargeMap[$item->comeform][$item->game_num][$item->area] ?? 0;
        }
        return successResp([
            'data' => $loginData,
            'last_page' => ceil($total/$request->page_size),
            'current_page' => (int)$request->page,
        ]);
    }

    /**
     * 登录充值汇总
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     * @throws \Illuminate\Validation\ValidationException
     */
    public function getLoginRechargeTotalStatistic(Request $request)
    {
        $this->validate($request, [
            'st' => ['required', 'date'],
            'et' => ['required', 'date'],
        ]);
        $st = $request->st;
        $et = $request->et;
        $area = $request->area;
        $comefrom = $request->comeform;

        if (strtotime($et) < strtotime($st)) {
            return errorResp('时间范围错误');
        }
        if (strtotime($et) - strtotime($st) > (86400*32)) {
            return errorResp('时间范围最大一个月');
        }

        $st .= ' 00:00:00';
        $et .= ' 23:59:59';

        $where = [];
        if ($area) {
            $where[] = ['area', 'like',"%{$area}%"];
        }
        if ($comefrom) {
            $where[] = ['comeform', '=',$comefrom];
        }
        $subQuery = DB::table('game_login')
            ->selectRaw('count(*) total, date(create_time) d')
            ->where('create_time', '>=', $st)
            ->where('create_time', '<=', $et)
            ->where('comeform', '<>', '0')
            ->where($where)
            ->groupByRaw('d');
        $totalLogin = DB::table(DB::raw("({$subQuery->toSql()}) as sub"))
            ->select(DB::raw('sum(sub.total) total_login'))
            ->mergeBindings($subQuery)
            ->value('total_login');
        $rechargeTotal = DB::table('game_recharge')
            ->where('create_time', '>=', $st)
            ->where('create_time', '<=', $et)
            ->where('comeform', '<>', '0')
            ->where($where)
            ->sum('amount');
        return successResp([
            'login_total' => $totalLogin,
            'recharge_total' => $rechargeTotal,
        ]);
    }

    public function showNewUserRechargeInfo()
    {
        return view('admin.dashboard.new_user_recharge');
    }

    public function getNewUserRechargeInfo(Request $request)
    {
        $currentPage = (int)$request->page;
        $pageSize = (int)$request->page_size;
        $zsfRecharge = DB::table('game_recharge')->selectRaw('distinct unionid')
            ->where('comeform', '=', 'zsf')->pluck('unionid')->toArray();
        $oldUserRes = DB::select("select distinct gl.unionid from game_login gl left join
 (select unionid, max(create_time) create_time from game_recharge where comeform = 'zsf' group by unionid) t ON gl.unionid = t.unionid
 where gl.comeform != 'zsf' and gl.create_time < t.create_time;");
        $oldUser = [];
        foreach ($oldUserRes as $v) {
            $oldUser[] = $v->unionid;
        }
        $newUnionIds = array_diff($zsfRecharge, $oldUser);
        // 计算起始索引位置
        $startIndex = ($currentPage - 1) * $pageSize;
        // 切片操作，提取指定范围内的元素
        $curUnionIds = array_slice($newUnionIds, $startIndex, $pageSize);
        $rechargeList = DB::table('game_recharge')
            ->selectRaw('unionid, sum(amount) total_recharge, count(*) total')
            ->whereIn('unionid', $curUnionIds)
            ->groupByRaw('unionid')
            ->get();
        $loginData = DB::table('game_login')
            ->selectRaw('unionid, count(*) total')
            ->whereIn('unionid', $curUnionIds)
            ->groupByRaw('unionid')
            ->pluck('total', 'unionid');
        // dump($rechargeList, $loginData);
        foreach ($rechargeList as &$item) {
            $item->login_total = $loginData[$item->unionid] ?? 0;
        }
        $count = count($newUnionIds);
        return successResp([
            'data' => $rechargeList,
            'total' => $count,
            'last_page' => ceil($count/$pageSize),
            'current_page' => $currentPage,
        ]);
    }
}
